﻿Imports KOR.Helpers

Namespace BL


    Public Module Users

#Region "Users_DML"
        Public Function InsertUser(dict As Dictionary(Of String, Object)) As Integer
            Return DDA.Insert("Users", dict)
        End Function

        Public Function UpdateUser(dict As Dictionary(Of String, Object), UserID As Integer) As Integer
            Return DDA.Update("Users", dict, "UserID=" & UserID)
        End Function

        Public Function DeleteUser(ByVal UserID As Integer) As Integer
            Return DDA.Delete("Users", "UserID=" & UserID, False)
        End Function
#End Region

#Region "Users_Selection"
        Public Function GetAllUsers() As IDataReader
            Dim sql As String = "SELECT * FROM Users"
            Return DDA.SelectData(sql)
        End Function

        Public Function GetUsers(searchFor As String, criteria As String, startRowIndex As Integer, maximumRows As Integer, SortBy As String) As IDataReader
            Dim sql As String
            sql = "SELECT * FROM Users "

            If SortBy = "" Then SortBy = " User ASC"

            Return DDA.SelectDataPaginated(sql, startRowIndex, maximumRows, SortBy)
        End Function

        Public Function GetUsersCount(searchFor As String, criteria As String) As Integer
            Dim sql As String
            sql = "SELECT * FROM Users "
            Return DDA.SelectDataCount(sql)
        End Function

        Public Function DoesUsernameExists(Username As String) As Boolean
            Dim sql As String = "SELECT * FROM Users WHERE Username = '" & Username & "' "
            Dim retVal As Boolean = False
            Using reader As IDataReader = DDA.SelectData(sql)
                If reader.Read Then
                    retVal = True
                End If
                reader.Close()
            End Using
            Return retVal
        End Function

        Public Function GetUserByUserID(UserID As String) As IDataReader
            Dim sql As String = "SELECT * FROM Users WHERE UserID = " & UserID
            Dim retVal As Boolean = False
            Return DDA.SelectData(sql)
        End Function


        Public Function AuthenticateUser(username As String, password As String) As Boolean
            Dim sql As String = "SELECT * FROM Users WHERE Username = @username and Password = @password"
            Dim retVal As Boolean = False
            Dim params As New Dictionary(Of String, Object)
            params.Add("username", username)
            params.Add("password", password)

            If DDA.SelectDataCount(sql, params) > 0 Then
                retVal = True
            Else
                retVal = False
            End If

            Return retVal
        End Function

        Public Function RoleOfUserByUserID(UserID As Integer) As String
            Dim retVal As String = ""
            Using reader As IDataReader = GetUserByUserID(UserID)
                If reader.Read Then
                    retVal = ValueOrDefault(reader("Role"), "").ToString
                End If
                reader.Close()
            End Using
            Return retVal
        End Function

        Public Function GetRolesByUsername(username As String) As String
            Dim sql As String = "SELECT * FROM Accounts WHERE Email = @Username "
            Dim params As New Dictionary(Of String, Object)
            params.Add("Username", username)

            Dim out As String = ""
            Using reader As IDataReader = DDA.SelectData(sql, params)
                If reader.Read Then out = ValueOrDefault(reader("Role"), "").ToString()
            End Using

            Return out
        End Function

        Public Function GetUserIDByUsername(username As String) As String
            Dim sql As String = "SELECT * FROM Users WHERE Username = @Username "
            Dim params As New Dictionary(Of String, Object)
            params.Add("Username", username)

            Dim out As Integer = 0
            Using reader As IDataReader = DDA.SelectData(sql, params)
                If reader.Read Then out = CInt(ValueOrDefault(reader("UseriD"), 0))
            End Using

            Return out
        End Function

        Public Function GetRoleIDByUsername(username As String) As Integer
            Dim sql As String = "SELECT Roles.* FROM Roles INNER JOIN Users ON Users.RoleID = Roles.UserTypeID " & _
                " WHERE Users.Username = '" & username & "'"
            Dim retVal As Integer = 0
            Using reader As IDataReader = DDA.SelectData(sql)
                If reader.Read Then
                    retVal = CInt(ValueOrDefault(reader("UserTypeID"), 0))
                End If
                reader.Close()
            End Using
            Return retVal
        End Function

        Public Function GetUserNameByUserID(userid As Integer, Optional isfull As Boolean = False, Optional FL As Boolean = False) As String
            Dim sql As String = "SELECT * FROM Users WHERE UserID = " & userid

            Dim retVal As String = ""
            Using reader As IDataReader = DDA.SelectData(sql)
                If reader.Read Then
                    If Not isfull Then
                        retVal = ValueOrDefault(reader("FirstName"), "").ToString
                    Else
                        If Not FL Then
                            retVal = ValueOrDefault(reader("LastName"), "").ToString.ConcatCSV(ValueOrDefault(reader("FirstName"), ""), ", ").ToString
                        Else
                            retVal = ValueOrDefault(reader("FirstName"), "").ToString.ConcatCSV(ValueOrDefault(reader("LastName"), ""), " ").ToString
                        End If
                    End If
                End If
                reader.Close()
            End Using
            Return retVal
        End Function

        Public Function GetRoleByUserID(UserID As Integer) As String
            Dim sql As String = "SELECT ROLES.* FROM Roles LEFT JOIN Users ON Users.RoleID = Roles.UserTypeID WHERE Users.UserID = " & UserID
            Dim retVal As String = ""
            Using reader As IDataReader = DDA.SelectData(sql)
                If reader.Read Then
                    retVal = ValueOrDefault(reader("Role"), "").ToString
                End If
                reader.Close()
            End Using
            Return retVal
        End Function



        'Public Function GetUserNameByUserID(UserID As Integer) As String
        '    Dim retVal As Boolean = False
        '    Dim Username As String = ""
        '    Dim sql As String = "SELECT * FROM  WHERE AccountID = " & AccountID
        '    Using reader As IDataReader = DDA.SelectData(sql)
        '        If reader.Read Then

        '            If ValueOrDefault(reader("CompanyName"), "").ToString = "" Then
        '                clientName = clientName.ConcatCSV(ValueOrDefault(reader("LastName"), "").ToString, " ")
        '                clientName = clientName.ConcatCSV(ValueOrDefault(reader("FirstName"), "").ToString, ", ")
        '            Else
        '                clientName = clientName.ConcatCSV(ValueOrDefault(reader("CompanyName"), "").ToString, " ")
        '            End If

        '        End If
        '    End Using
        '    Return clientName
        'End Function

#End Region
    End Module

End Namespace
